I'm trying to use placeholders with DBD::Mock and I keep getting the same results back. I've been reading and rereading the DBD::Mock documentation hoping to figure out what I'm doing wrong. I don't want to use the session stuff because I don't want to be stuck with a predetermined order that the queries have to follow. Does anyone have any tips on how to do this or perhaps a different approach that I'm missing?
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::Mock;
my $dbh = get_dbh();
_setup_some_table( $dbh );
for my $id (reverse (1 .. 2)) {
my @some = get_some_table($dbh, $id);
print "@some\n";
}
sub get_dbh {
my $dbh = DBI->connect( "DBI:Mock:", "", "", );
return $dbh;
}
sub get_some_table {
my $dbh = shift;
my $key = shift;
my $sql = q{SELECT * FROM SOME_TABLE WHERE ID=?};
my $sth = $dbh->prepare( $sql );
$sth->execute( $key );
my $row = $sth->fetchrow_arrayref();
return wantarray ? @$row : $row;
}
sub _setup_some_table {
my $dbh = shift;
my @columns = qw( id who what when where why );
my %data = (
1 => [ qw( 1 joe ball now park fun ) ],
2 => [ qw( 2 sam hamburger tomorrow restaurant food ) ],
);
for my $id (keys %data) {
$dbh->{mock_add_resultset} = {
sql => qq{SELECT * FROM SOME_TABLE WHERE ID=?},
bound_params => [ $id ],
results => [ \@columns, $data{$id} ],
};
}
}
__END__
D:\>some_mock.pl
2 sam hamburger tomorrow restaurant food
2 sam hamburger tomorrow restaurant food
Update: I don't know why I have the bound_params in the mock_add_resultset calls because they don't get used unless you are using the sessions.
Okay, I've got some code that works but I'm not happy with it. Talk about a kludge.
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use DBD::Mock;
{
my @columns = qw( id who what when where why );
my %data = (
'1' => [ qw( 1 joe ball now park fun ) ],
'2' => [ qw( 2 sam hamburger tomorrow restaurant food ) ],
);
sub setup_some_table {
my $dbh = shift;
my $id = shift;
$dbh->{mock_add_resultset} = {
sql => qq{SELECT * FROM SOME_TABLE WHERE ID=?},
results => [ \@columns, $data{$id} ],
};
}
}
sub get_dbh {
my $dbh = DBI->connect( "DBI:Mock:", "", "", );
return $dbh;
}
sub get_some_table {
my $dbh = shift;
my $id = shift;
setup_some_table( $dbh, $id );
my $sql = q{SELECT * FROM SOME_TABLE WHERE ID=?};
my $sth = $dbh->prepare( $sql );
$sth->execute( $id );
my $row = $sth->fetchrow_arrayref();
return wantarray ? @$row : $row;
}
my $dbh = get_dbh();
for my $id (1.. 2) {
my @some = get_some_table($dbh, $id);
print "@some\n";
}
__END__
D:/some_mock.pl
1 joe ball now park fun
2 sam hamburger tomorrow restaurant food
I might have to look under the hood and see how easy it will be to add support for placeholders.
Re:Not happy
Stevan on 2005-11-10T23:00:40
I think what you are looking for is the result set to be chosen based on both the SQL statement (which is how it currently works) and by the set of specific bound param values (which is not currently how it works). This probably wouldn't be too hard to implement actually, if I get some tuits, I will look into it this week. And of course, patches are always welcome
:) The reason your first example did not work was because the result sets are stored with the SQL statement as the key. So when you saw the same results coming back, it was because you had actually overwritten the first mock resultset with the second mock resultset.
BTW - the DBD::Mock::Session itself only checks to be sure that you have supplied the right bound parameters, it does not match the current state based on those bound param values though. Here is the exert from the docs:
Also, as can be seen in the the session element, bound parameters can also be supplied and tested. In this statement, the SQL is compared, then when the statement is executed, the bound parameters are also checked. The bound parameters much match in both number of parameters and the parameters themselves, or an error will be raised.- Stevan
Re:Not happy
Mr. Muskrat on 2005-11-11T02:00:06
Yeah, I have already started looking at the code. Once I did it was pretty obvious why it wasn't working as I wanted. This weekend, I'll look in the cupboard and see if I have any more tuits.Not so easy
Mr. Muskrat on 2005-11-15T16:07:18
I have been looking at the source and I must say that this doesn't look as easy to implement as I thought it would be. I haven't given up but it may take a while. I'm discovering just how little I know about how the database modules work behind the scenes.:) Re:Not happy
Mr. Muskrat on 2005-12-27T03:34:20
No rush. I have no use for this at the moment (I've changed jobs) but I still think it would be an awesome feature to add.